package net.knightwatch.kwtools.database;

import java.io.ByteArrayOutputStream;
import java.io.FileInputStream;
import java.io.IOException;
import java.text.DateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;
import net.knightwatch.kwtools.customers.Address;
import net.knightwatch.kwtools.customers.Building;
import net.knightwatch.kwtools.customers.Customer;
import net.knightwatch.kwtools.walkthrough.Potential;
import net.knightwatch.kwtools.walkthrough.items.Answer;
import net.knightwatch.kwtools.walkthrough.items.AutoCompleteAnswer;
import net.knightwatch.kwtools.walkthrough.items.BooleanAnswer;
import net.knightwatch.kwtools.walkthrough.items.Item;
import net.knightwatch.kwtools.walkthrough.items.ItemType;
import net.knightwatch.kwtools.walkthrough.items.PhotoAnswer;
import net.knightwatch.kwtools.walkthrough.items.Question;
import net.knightwatch.kwtools.walkthrough.items.RadioButtonAnswer;
import net.knightwatch.kwtools.walkthrough.items.SpinnerAnswer;
import net.knightwatch.kwtools.walkthrough.items.TextAnswer;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.graphics.Bitmap;
import android.graphics.BitmapFactory;
import android.util.Log;
import android.widget.Toast;

public class KwToolsDatasource{
	
	private static final String TAG = "KwToolsDatasource";
	// Database Fields
	private SQLiteDatabase database;
	private DatabaseHelper dbHelper;
	private Context context;
	
	// Constructor
	public KwToolsDatasource(Context context){
		// Pass context from constructor to the helper class and create it's instance
		dbHelper = new DatabaseHelper(context);
		this.context = context;
	}
	
	private byte[] bitmaptoByteArray(Bitmap photo) {
		// Method to convert the photo to a byteArray. 
        ByteArrayOutputStream stream = new ByteArrayOutputStream();
        photo.compress(Bitmap.CompressFormat.PNG, 100, stream);
        byte[] byteArray = stream.toByteArray();
        return byteArray;
	}
	private Bitmap byteArraytoBitmap(byte[] bytes){
		// Method to convert the byteArray back to a photo
		Log.d(TAG, "Converting byte array to bitmap length: " + bytes.length);
		Bitmap photo = BitmapFactory.decodeByteArray(bytes , 0, bytes.length);
		return photo;
	}
	
	// Close the database
	public void Close(){
		dbHelper.close();
	}
	
	public Address createAddress(Address address) {
		// TODO Insert the address into the database. 
		ContentValues values = new ContentValues();
		values.put(AddressTable.COLUMN_CITY, address.getCity());
		values.put(AddressTable.COLUMN_COUNTRY, address.getCountry());
		values.put(AddressTable.COLUMN_STATE, address.getState());
		values.put(AddressTable.COLUMN_STREET, address.getStreet());
		values.put(AddressTable.COLUMN_ZIPCODE, address.getZipcode());
		address.setAddressId(database.insert(AddressTable.TABLE_ADDRESS, null, values));
		return address;
	}
	public long createAutoCompleteAnswer(long itemId, long customerId,
			Long questionId, AutoCompleteAnswer autoCompleteAnswer) {
		// Open the database
				
				// Start the database transaction
				database.beginTransaction();
				try {
					ContentValues values = new ContentValues();
					// Insert into the answer table first
					values.put(AnswerTable.COLUMN_ANSWER_TYPE, Answer.TEXT_TYPE);
					values.put(AnswerTable.COLUMN_CUSTOMER_ID, customerId);
					values.put(AnswerTable.COLUMN_ITEM_ID, itemId);
					values.put(AnswerTable.COLUMN_QUESTION_ID, questionId);
					autoCompleteAnswer.setAnswerId(database.insert(AnswerTable.TABLE_ANSWERS, null, values));
					// Clear the values and insert the remainder into the text answer table
					values.clear();
					values.put(AutoCompleteAnswersTable.COLUMN_ANSWER_ID, autoCompleteAnswer.getAnswerId());
					values.put(AutoCompleteAnswersTable.COLUMN_ANSWER_TEXT, autoCompleteAnswer.getAnswer());
					database.insert(AutoCompleteAnswersTable.TABLE_AUTOCOMPLETE_ANSWERS, null, values);
					// Set the transaction successful
					database.setTransactionSuccessful();
				} catch (Exception e) {
					// Time to log and display errors
					Log.e(TAG, "Unable to save AutoComplete Answer to the database. Error in createAutoCompleteAnswer() in kwtoolsdatasource");
				} finally{
					// Roll back the transaction
					database.endTransaction();
				}
				// Close the database
				return autoCompleteAnswer.getAnswerId();
	}
	private void createAutoCompleteQuestion(long itemTypeId, String question, int parentId, String hintText) throws SQLException {
		// TODO Auto-generated method stub
		ContentValues values = new ContentValues();
		long questionId = createQuestion(itemTypeId, question, parentId, Answer.AUTOCOMPLETE_TYPE);
		values.put(AutoCompleteQuestionTable.COLUMN_QUESTION_ID, questionId);
		values.put(AutoCompleteQuestionTable.COLUMN_HINT_TEXT, hintText);
		if(database.insert(AutoCompleteQuestionTable.TABLE_AUTO_COMPLETE_QUESTIONS, null, values)<0){
			throw new SQLException();
		}
	}

	public long createBooleanAnswer(long itemId, long customerId,
			Long questionId, BooleanAnswer booleanAnswer) {
		// method to create a Boolean Answer in the database
		
		database.beginTransaction();
		try{
			ContentValues values = new ContentValues();
			values.clear();
			values.put(AnswerTable.COLUMN_ITEM_ID, itemId);
			values.put(AnswerTable.COLUMN_ANSWER_TYPE, Answer.BOOLEAN_TYPE);
			values.put(AnswerTable.COLUMN_CUSTOMER_ID, customerId);
			values.put(AnswerTable.COLUMN_QUESTION_ID, questionId);
			booleanAnswer.setAnswerId(database.insert(AnswerTable.TABLE_ANSWERS, null, values));
			values.clear();
			values.put(BooleanAnswersTable.COLUMN_VALUE, booleanAnswer.getValue());
			values.put(BooleanAnswersTable.COLUMN_ANSWER_ID, booleanAnswer.getAnswerId());
			values.put(BooleanAnswersTable.COLUMN_ANSWER_TEXT, booleanAnswer.getAnswer());
			// Insert the Boolean Answer
			database.insert(BooleanAnswersTable.TABLE_BOOLEAN_ANSWERS, null, values);
			database.setTransactionSuccessful();
        } catch (Exception e) {
            Log.e(TAG, "Unable to save boolean value to database. Error in createBooleanAnswer() in kwtoolsdatasource");
        	//Error in between database transaction 
        }finally {
                database.endTransaction();
        }
		return booleanAnswer.getAnswerId();
	}

	private void createBooleanQuestion(long itemTypeId, String question, int parentId, String trueText,
			String falseText) throws SQLException {
		// TODO Auto-generated method stub
		ContentValues values = new ContentValues();
		long questionId = createQuestion(itemTypeId, question, parentId, Answer.BOOLEAN_TYPE);
		values.put(BooleanQuestionTable.COLUMN_QUESTION_ID, questionId);
		values.put(BooleanQuestionTable.COLUMN_TRUE_TEXT, trueText);
		values.put(BooleanQuestionTable.COLUMN_FALSE_TEXT, falseText);
		values.put(BooleanQuestionTable.COLUMN_DEFAULT_VALUE, false);
		if(database.insert(BooleanQuestionTable.TABLE_BOOLEAN_QUESTIONS, null, values)<0){
			throw new SQLException();
		}
	}

	public Building createBuilding(Building building){
		// Save the address first
		database.beginTransaction();
		try{
			// If the Address ID already exists do not create a new address.
			if(!(building.getAddress().getAddressId() > 0)){
				building.setAddress(createAddress(building.getAddress()));
			} 
			// Save the building
			ContentValues values = new ContentValues();
			values.put(BuildingTable.COLUMN_CUSTOMERID, building.getCustomerId());
			values.put(BuildingTable.COLUMN_NAME, building.getName());
			values.put(BuildingTable.COLUMN_ADDRESSID, building.getAddress().getAddressId());
			values.put(BuildingTable.COLUMN_GPS_LATITUDE, building.getpLatitude());
			values.put(BuildingTable.COLUMN_GPS_LONGITUDE, building.getpLongitude());
			values.put(BuildingTable.COLUMN_GPS_ALTITUDE, building.getpAltitude());
			Log.v(TAG, "Saving Building " + building.getName() + " to database");
			building.setId(database.insert(BuildingTable.TABLE_BUILDING, null, values));
			database.setTransactionSuccessful();
		} catch (SQLException e){
			e.printStackTrace();
			Log.e(TAG, "Unable to add new building to the database");
		} finally{
			database.endTransaction();
		}
		return building;
	}

	// CRUD Operations here.
	// Create New Customer
	/**
	 * Create a customer by passing the customer object to the createCustomer function. It will write the entry to the database, get the new ID created by the database
	 * and write that back to the object that it returns to the use.
	 * @param customer
	 * @return Customer object
	 */
	public Customer createCustomer(Customer customer){
		// Open the connection
		
		ContentValues values = new ContentValues();
		values.put(CustomerTable.COLUMN_NAME, customer.getCustomerName());
		// FIXME Need to correct the formatting of the datetime to the correct yyyy-mm-dd hh:mm:ss format required for the sql feild
		values.put(CustomerTable.COLUMN_CREATE_TIME, DateFormat.getDateTimeInstance().format(new Date()));
		Log.d(TAG, "Saving Customer " + customer.getCustomerName() + " to database");
		customer.setCustomerId(database.insert(CustomerTable.TABLE_CUSTOMER, null, values));
		
		return customer;
	}

	public Item createItem(Item item) {
		// Open the database connection
		
		ContentValues values = new ContentValues();
		values.put(ItemTable.COLUMN_POTENTIAL_ID, item.getPotentialId());
		values.put(ItemTable.COLUMN_ITEM_TYPE_ID, item.getItemType().getItemTypeId());
		values.put(ItemTable.COLUMN_LATITUDE, item.getLatitude());
		values.put(ItemTable.COLUMN_LONGITUDE, item.getLongitude());
		values.put(ItemTable.COLUMN_ALTITUDE, item.getAltitude());
		values.put(ItemTable.COLUMN_ELAPSED_TIME, item.getTimeElapsedMillis());
		values.put(ItemTable.COLUMN_NAME, item.getName());
		// Insert the item into the database
		try{
			item.setItemId(database.insert(ItemTable.TABLE_ITEM, null, values));
			Log.d(TAG, "New Item Created ID = " + item.getItemId());
		} catch (Exception e){
			Log.e(TAG, "Error inserting item into database.");
		}
		
		return item;
	}

	/**
	 * Create a Photo Answer in the database. 
	 * @param itemId database id of the item
	 * @param customerId database id of the customer
	 * @param questionId database id of the question
	 * @param photoAnswer the full answer package for a photo
	 * @return 
	 */
	public long createPhotoAnswer(long itemId, long customerId, Long questionId,
			PhotoAnswer photoAnswer) {
		
		database.beginTransaction();
		try{
			byte[] byteArray;
			byteArray = bitmaptoByteArray(photoAnswer.getPhoto());
			ContentValues values = new ContentValues();
			values.clear();
			values.put(AnswerTable.COLUMN_ITEM_ID, itemId);
			values.put(AnswerTable.COLUMN_ANSWER_TYPE, Answer.PHOTO_TYPE);
			values.put(AnswerTable.COLUMN_CUSTOMER_ID, customerId);
			values.put(AnswerTable.COLUMN_QUESTION_ID, questionId);
			// Create a answer
			photoAnswer.setAnswerId(database.insert(AnswerTable.TABLE_ANSWERS, null, values));
			// Store the photo
			values.clear();
			values.put(PhotoAnswerTable.COLUMN_ANSWER_ID, photoAnswer.getAnswerId());
			values.put(PhotoAnswerTable.COLUMN_PHOTO_ANSWER, byteArray);
			photoAnswer.setAnswerId((database.insert(PhotoAnswerTable.TABLE_PHOTO_ANSWERS, null, values)));
			values.clear();
        	database.setTransactionSuccessful();
        } catch (Exception e) {
            Log.e(TAG, "Unable to save photo to database. Error in savePhotoAnser() in kwtoolsdatasource");
        	//Error in between database transaction 
        }finally {
                database.endTransaction();
        }		
		return photoAnswer.getAnswerId();
	}
	

	private void createPhotoQuestion(long itemTypeId, String question, int parentId, String directions) throws SQLException {
		// TODO Auto-generated method stub
		ContentValues values = new ContentValues();
		long questionId = createQuestion(itemTypeId, question, parentId, Answer.PHOTO_TYPE);
		values.put(PhotoQuestionTable.COLUMN_QUESTION_ID, questionId);
		values.put(PhotoQuestionTable.COLUMN_DIRECTIONS, directions);
		if(database.insert(PhotoQuestionTable.TABLE_PHOTO_QUESTIONS, null, values)<0){
			throw new SQLException();
		}
	}

	public Potential createPotential(Potential potential) {
		// Open the database
		// Set the creation date of the potential to now. 
		
		potential.setMsCreateTime(System.currentTimeMillis());
		ContentValues values = new ContentValues();
		// If the Address ID already exists do not create a new address.
		values.put(PotentialTable.COLUMN_NAME, potential.getName());
		values.put(PotentialTable.COLUMN_CUSTOMER_ID, potential.getCustomerId());
		values.put(PotentialTable.COLUMN_COMPLETED, potential.getComplete());
		values.put(PotentialTable.COLUMN_BUILDING_ID, potential.getBuildingId());
		values.put(PotentialTable.COLUMN_PROJECTED_START, potential.getMsProjectedStartDate());
		values.put(PotentialTable.COLUMN_SCOPE_OF_WORK, potential.getScopeOfWork());
		values.put(PotentialTable.COLUMN_CREATED, potential.getMsCreateTime());
		try{
			Log.d(TAG, "Inserting potential into database");
			potential.setId(database.insert(PotentialTable.TABLE_POTENTIAL, null, values));
		} catch (SQLException e){
			e.printStackTrace();
			Log.e(TAG, "Unable to create the new potential");
		}
		return potential;
	}

	private long createQuestion(long itemTypeId, String question, int parentId, int questionType) {
		// TODO Auto-generated method stub
		ContentValues values = new ContentValues();
		long questionId;
		values.put(QuestionTable.COLUMN_ITEM_TYPE_ID, itemTypeId);
		values.put(QuestionTable.COLUMN_QUESTION, question);
		values.put(QuestionTable.COLUMN_PARENT_ID, parentId);
		values.put(QuestionTable.COLUMN_ANSWER_TYPE_ID, questionType);
		questionId = database.insert(QuestionTable.TABLE_QUESTION, null, values);
		return questionId;
	}

	public long createRadioButtonAnswer(long itemId, long customerId,
			long questionId, RadioButtonAnswer radioButtonAnswer) {
		// Open the database
		
		// Start the database transaction
		database.beginTransaction();
		try {
			ContentValues values = new ContentValues();
			// Insert into the answer table first
			values.put(AnswerTable.COLUMN_ANSWER_TYPE, Answer.TEXT_TYPE);
			values.put(AnswerTable.COLUMN_CUSTOMER_ID, customerId);
			values.put(AnswerTable.COLUMN_ITEM_ID, itemId);
			values.put(AnswerTable.COLUMN_QUESTION_ID, questionId);
			radioButtonAnswer.setAnswerId(database.insert(AnswerTable.TABLE_ANSWERS, null, values));
			// Clear the values and insert the remainder into the text answer table
			values.clear();
			values.put(RadioButtonAnswersTable.COLUMN_ANSWER_TEXT, radioButtonAnswer.getAnswer());
			values.put(RadioButtonAnswersTable.COLUMN_ANSWER_ID, radioButtonAnswer.getAnswerId());
			// Insert into the RadioButtonAnswers Table. 
			database.insert(RadioButtonAnswersTable.TABLE_RADIOBUTTON_ANSWERS, null, values);
			// Set the transaction successful
			database.setTransactionSuccessful();
		} catch (Exception e) {
			// Time to log and display errors
			Log.e(TAG, "Unable to save Radio Button Answer to the database. Error in createRadioButtonAnswer() in kwtoolsdatasource");
		} finally{
			// Roll back the transaction
			database.endTransaction();
		}
		// Close the database
		return radioButtonAnswer.getAnswerId();
		
	}

	private void createRadioButtonQuestion(long itemTypeId, String question, int parentId, ArrayList<String> options) throws SQLException {
		ContentValues values = new ContentValues();
		long questionId = createQuestion(itemTypeId, question, parentId, Answer.RADIOBUTTON_TYPE);
		int i = 0;
		for(String option : options){ 
			values.clear();
			values.put(RadioButtonQuestionTable.COLUMN_QUESTION_ID, questionId);
			values.put(RadioButtonQuestionTable.COLUMN_OPTION_TEXT, option);
			values.put(RadioButtonQuestionTable.COLUMN_OPTION_ID, i);
			if(database.insert(RadioButtonQuestionTable.TABLE_RADIOBUTTONQUESTIONS, null, values)<0){
				throw new SQLException();
			}
			i++;
		}
	}

	public long createSpinnerAnswer(long itemId, long customerId,
			Long questionId, SpinnerAnswer spinnerAnswer) {
		// method to create a Spinner Answer in the database
				// Open the database
				
				// Start the database transaction
				database.beginTransaction();
				try {
					ContentValues values = new ContentValues();
					// Insert into the answer table first
					values.put(AnswerTable.COLUMN_ANSWER_TYPE, Answer.TEXT_TYPE);
					values.put(AnswerTable.COLUMN_CUSTOMER_ID, customerId);
					values.put(AnswerTable.COLUMN_ITEM_ID, itemId);
					values.put(AnswerTable.COLUMN_QUESTION_ID, questionId);
					spinnerAnswer.setAnswerId(database.insert(AnswerTable.TABLE_ANSWERS, null, values));
					// Clear the values and insert the remainder into the text answer table
					values.clear();
					values.put(SpinnerAnswersTable.COLUMN_OPTION_ID, spinnerAnswer.getOptionId());
					values.put(SpinnerAnswersTable.COLUMN_ANSWER_TEXT, spinnerAnswer.getAnswer());
					values.put(SpinnerAnswersTable.COLUMN_ANSWER_ID, spinnerAnswer.getAnswerId());
					// Insert into the Spinner Answers Table.
					database.insert(SpinnerAnswersTable.TABLE_SPINNER_ANSWERS, null, values);
					Log.d(TAG, "Answer inserted into the SpinnerAnswersTable");
					// Set the transaction successful
					database.setTransactionSuccessful();
				} catch (Exception e) {
					// Time to log and display errors
					Log.e(TAG, "Unable to save Text Answer to the database. Error in createTextAnswer() in kwtoolsdatasource");
				} finally{
					// Roll back the transaction
					database.endTransaction();
				}
				// Close the database
				return spinnerAnswer.getAnswerId();
	}

	private void createSpinnerQuestion(long itemTypeId, String question, int parentId, ArrayList<String> options) throws SQLException {
		// TODO Auto-generated method stub
		ContentValues values = new ContentValues();
		long questionId = createQuestion(itemTypeId, question, parentId, Answer.SPINNER_TYPE);
		for (String s : options){
			values.put(SpinnerQuestionTable.COLUMN_QUESTION_ID, questionId);
			values.put(SpinnerQuestionTable.COLUMN_OPTION_TEXT, s);
			if(database.insert(SpinnerQuestionTable.TABLE_SPINNER_QUESTION, null, values)<0){
				throw new SQLException();
			}				
			values.clear();
		}
			
	}

	public long createTextAnswer(long itemId, long customerId, Long questionId,
			TextAnswer textAnswer) {
		// Open the database
		
		// Start the database transaction
		database.beginTransaction();
		try {
			ContentValues values = new ContentValues();
			// Insert into the answer table first
			values.put(AnswerTable.COLUMN_ANSWER_TYPE, Answer.TEXT_TYPE);
			values.put(AnswerTable.COLUMN_CUSTOMER_ID, customerId);
			values.put(AnswerTable.COLUMN_ITEM_ID, itemId);
			values.put(AnswerTable.COLUMN_QUESTION_ID, questionId);
			textAnswer.setAnswerId(database.insert(AnswerTable.TABLE_ANSWERS, null, values));
			// Clear the values and insert the remainder into the text answer table
			values.clear();
			values.put(TextAnswersTable.COLUMN_ANSWER_ID, textAnswer.getAnswerId());
			values.put(TextAnswersTable.COLUMN_ANSWER_TEXT, textAnswer.getAnswerValue());
			database.insert(TextAnswersTable.TABLE_TEXT_ANSWERS, null, values);
			// Set the transaction successful
			database.setTransactionSuccessful();
		} catch (Exception e) {
			// Time to log and display errors
			Log.e(TAG, "Unable to save Text Answer to the database. Error in createTextAnswer() in kwtoolsdatasource");
		} finally{
			// Roll back the transaction
			database.endTransaction();
		}
		// Close the database
		return textAnswer.getAnswerId();
		
		
	}
	private void createTextQuestion(long itemTypeId, String question, int parentId, String hintText, int valueType) throws SQLException {
		ContentValues values = new ContentValues();
		long questionId = createQuestion(itemTypeId, question, parentId, Answer.TEXT_TYPE);
		values.put(TextQuestionTable.COLUMN_HINT_TEXT, hintText);
		values.put(TextQuestionTable.COLUMN_QUESTION_ID, questionId);
		values.put(TextQuestionTable.COLUMN_VALUE_TYPE, valueType);
		if(database.insert(TextQuestionTable.TABLE_TEXTQUESTIONS, null, values)<0){
			throw new SQLException();		
		}
	}

	private Address cursorToAddress(Cursor cursor) {
		Address address = new Address();
		address.setAddressId(cursor.getLong(cursor.getColumnIndex(AddressTable.COLUMN_ID)));
		address.setCity(cursor.getString(cursor.getColumnIndex(AddressTable.COLUMN_CITY)));
		address.setCountry(cursor.getString(cursor.getColumnIndex(AddressTable.COLUMN_COUNTRY)));
		address.setStreet(cursor.getString(cursor.getColumnIndex(AddressTable.COLUMN_STREET)));
		address.setState(cursor.getString(cursor.getColumnIndex(AddressTable.COLUMN_STATE)));
		address.setZipcode(cursor.getString(cursor.getColumnIndex(AddressTable.COLUMN_ZIPCODE)));
		Log.d(TAG, "Returning Address ID = " + address.getAddressId());
		return address;
	}

	private AutoCompleteAnswer cursorToAutoCompleteAnswer(Cursor cursor) {
		// Convert cursor to AutoCompleteAnswer
		AutoCompleteAnswer autoCompleteAnswer = new AutoCompleteAnswer();
		autoCompleteAnswer.setAnswerId(cursor.getLong(cursor.getColumnIndex(AutoCompleteAnswersTable.COLUMN_ANSWER_ID)));
		autoCompleteAnswer.setAnswerTypeId(Answer.AUTOCOMPLETE_TYPE);
		autoCompleteAnswer.setAnswer(cursor.getString(cursor.getColumnIndex(AutoCompleteAnswersTable.COLUMN_ANSWER_TEXT)));
		return autoCompleteAnswer;
	}

	private AutoCompleteAnswer cursorToAutoCompleteQuestion(Cursor cursor) {
		// TODO Auto-generated method stub
		AutoCompleteAnswer autoCompleteAnswer = new AutoCompleteAnswer(cursor.getLong(cursor.getColumnIndex(AutoCompleteQuestionTable.COLUMN_QUESTION_ID)));
		autoCompleteAnswer.setAnswerTypeId(Answer.AUTOCOMPLETE_TYPE);
		autoCompleteAnswer.setHintText(cursor.getString(cursor.getColumnIndex(AutoCompleteQuestionTable.COLUMN_HINT_TEXT)));
		return autoCompleteAnswer;
	}

	private BooleanAnswer cursorToBooleanAnswer(Cursor cursor) {
		// Convert cursor to Radio Button Answer
		BooleanAnswer booleanAnswer = new BooleanAnswer();
		booleanAnswer.setAnswerId(cursor.getLong(cursor.getColumnIndex(BooleanAnswersTable.COLUMN_ANSWER_ID)));
		booleanAnswer.setAnswerTypeId(Answer.BOOLEAN_TYPE);
		booleanAnswer.setValue(cursor.getInt(cursor.getColumnIndex(BooleanAnswersTable.COLUMN_VALUE)) > 0);
		booleanAnswer.setAnswer(cursor.getString(cursor.getColumnIndex(BooleanAnswersTable.COLUMN_ANSWER_TEXT)));
		return booleanAnswer;
	}

	private Building cursorToBuilding(Cursor cursor) {
		Building building = new Building();
		// Lookup the address from the database and add to the building.
		building.setId(cursor.getLong(cursor.getColumnIndex(BuildingTable.COLUMN_ID)));
		building.setCustomerId(cursor.getLong(cursor.getColumnIndex(BuildingTable.COLUMN_CUSTOMERID)));
		building.setpLatitude(cursor.getDouble(cursor.getColumnIndex(BuildingTable.COLUMN_GPS_LATITUDE)));
		building.setpLongitude(cursor.getDouble(cursor.getColumnIndex(BuildingTable.COLUMN_GPS_LONGITUDE)));
		building.setpAltitude(cursor.getDouble(cursor.getColumnIndex(BuildingTable.COLUMN_GPS_ALTITUDE)));
		building.setName(cursor.getString(cursor.getColumnIndex(BuildingTable.COLUMN_NAME)));
		building.setAddress(getAddress(cursor.getLong(cursor.getColumnIndex(BuildingTable.COLUMN_ADDRESSID))));
		Log.d(TAG, "Returning Building ID = " + building.getId());
		return building;
	}

	// Convert a cursor to a customer
	/**
	 * Private Function to create a customer from a cursor. 
	 * @param cursor
	 * @return Customer Object
	 */
	private Customer cursorToCustomer(Cursor cursor) {
		Customer customer = new Customer();
		customer.setCustomerId(cursor.getLong(cursor.getColumnIndex(CustomerTable.COLUMN_ID)));
		customer.setCustomerName(cursor.getString(cursor.getColumnIndex(CustomerTable.COLUMN_NAME)));
		return customer;
	}

	private Item cursorToItem(Cursor cursor) {
		Item item = new Item();
		item.setItemId(cursor.getLong(cursor.getColumnIndex(ItemTable.COLUMN_ID)));
		item.setAltitude(cursor.getDouble(cursor.getColumnIndex(ItemTable.COLUMN_ALTITUDE)));
		item.setLatitude(cursor.getDouble(cursor.getColumnIndex(ItemTable.COLUMN_LATITUDE)));
		item.setLongitude(cursor.getDouble(cursor.getColumnIndex(ItemTable.COLUMN_LONGITUDE)));
		item.setName(cursor.getString(cursor.getColumnIndex(ItemTable.COLUMN_NAME)));
		item.setPotentialId(cursor.getLong(cursor.getColumnIndex(ItemTable.COLUMN_POTENTIAL_ID)));
		// Lookup the item type and add it as well. 
		item.setItemType(getItemTypeById(cursor.getLong(cursor.getColumnIndex(ItemTable.COLUMN_ITEM_TYPE_ID))));
		return item;
	}

	private ItemType cursorToItemType(Cursor cursor) {
		ItemType itemType = new ItemType();
		itemType.setItemTypeId(cursor.getLong(cursor.getColumnIndex(ItemTypeTable.COLUMN_ID)));
		itemType.setName(cursor.getString(cursor.getColumnIndex(ItemTypeTable.COLUMN_NAME)));
		return itemType;
	}
	private PhotoAnswer cursorToPhotoAnswer(Cursor cursor) {
		// TODO Auto-generated method stub
		PhotoAnswer photoAnswer = new PhotoAnswer();
		photoAnswer.setAnswerId(cursor.getLong(cursor.getColumnIndex(PhotoAnswerTable.COLUMN_ANSWER_ID)));
		photoAnswer.setAnswerTypeId(Answer.PHOTO_TYPE);
		photoAnswer.setPhoto(byteArraytoBitmap(cursor.getBlob(cursor.getColumnIndex(PhotoAnswerTable.COLUMN_PHOTO_ANSWER))));
		return photoAnswer;
	}

	private PhotoAnswer cursorToPhotoQuestion(Cursor cursor) {
		// TODO Auto-generated method stub
		PhotoAnswer photoAnswer = new PhotoAnswer();
		photoAnswer.setDirections(cursor.getString(cursor.getColumnIndex(PhotoQuestionTable.COLUMN_DIRECTIONS)));
		photoAnswer.setAnswerTypeId(Answer.PHOTO_TYPE);
		try {
			photoAnswer.setExamplePhoto(byteArraytoBitmap(cursor.getBlob(cursor.getColumnIndex(PhotoQuestionTable.COLUMN_EXAMPLE_PHOTO))));
		} catch (Exception e) {
			Log.e(TAG, "Unable to convert sqlite blob to bitmap");
			e.printStackTrace();
		}
		return photoAnswer;
	}

	private Potential cursorToPotential(Cursor cursor) {
		Potential potential = new Potential();
		// Convert a int from sqlite to boolean. If 1 > 0 then it is True else false
		potential.setId(cursor.getLong(cursor.getColumnIndex(PotentialTable.COLUMN_ID)));
		potential.setBuildingId(cursor.getLong(cursor.getColumnIndex(PotentialTable.COLUMN_BUILDING_ID)));
		potential.setCustomerId(cursor.getLong(cursor.getColumnIndex(PotentialTable.COLUMN_CUSTOMER_ID)));
		potential.setName(cursor.getString(cursor.getColumnIndex(PotentialTable.COLUMN_NAME)));
		potential.setComplete(cursor.getInt((cursor.getColumnIndex(PotentialTable.COLUMN_COMPLETED)))>0);
		potential.setMsCreateTime(cursor.getLong(cursor.getColumnIndex(PotentialTable.COLUMN_CREATED)));
		potential.setMsProjectedStartDate(cursor.getLong(cursor.getColumnIndex(PotentialTable.COLUMN_PROJECTED_START)));
		potential.setScopeOfWork(cursor.getString(cursor.getColumnIndex(PotentialTable.COLUMN_SCOPE_OF_WORK)));
		// Lookup the count of the items. 
		return potential;
	}

	private Question cursorToQuestion(Cursor cursor) {
		Question question = new Question();
		question.setQuestionId(cursor.getLong(cursor.getColumnIndex(QuestionTable.COLUMN_ID)));
		question.setParentId(cursor.getLong(cursor.getColumnIndex(QuestionTable.COLUMN_PARENT_ID)));
		question.setQuestion(cursor.getString(cursor.getColumnIndex(QuestionTable.COLUMN_QUESTION)));
		Answer answer;
		switch (cursor.getInt(cursor.getColumnIndex(QuestionTable.COLUMN_ANSWER_TYPE_ID))) {
			case Answer.PHOTO_TYPE:
				Log.d(TAG, "PHOTO_TYPE");
				answer = getPhotoAnswer(question.getQuestionId());
				break;
			case Answer.AUTOCOMPLETE_TYPE:
				Log.d(TAG, "AUTOCOMPLETE_TYPE");
				answer = getAutoCompleteAnswer(question.getQuestionId());
				break;
			case Answer.SPINNER_TYPE:
				Log.d(TAG, "PICKLIST_TYPE");
				answer = getPickListAnswer(question.getQuestionId());
				break;
			case Answer.TEXT_TYPE:
				answer = getTextAnswer(question.getQuestionId());
				break;
			case Answer.RADIOBUTTON_TYPE:
				answer = getRadioButtonAnswer(question.getQuestionId());
				break;
			case Answer.BOOLEAN_TYPE:
				answer = getBooleanTypeAnswer(question.getQuestionId());
				break;
			default:
				answer = new TextAnswer();
				break;
		}
		question.setAnswer(answer);
		return question;
	}

	private RadioButtonAnswer cursorToRadioButtonAnswer(Cursor cursor) {
		// Convert cursor to Radio Button Answer
		RadioButtonAnswer radioButtonAnswer = new RadioButtonAnswer();
		radioButtonAnswer.setAnswerId(cursor.getLong(cursor.getColumnIndex(RadioButtonAnswersTable.COLUMN_ANSWER_ID)));
		radioButtonAnswer.setAnswerTypeId(Answer.RADIOBUTTON_TYPE);
		radioButtonAnswer.setAnswer(cursor.getString(cursor.getColumnIndex(RadioButtonAnswersTable.COLUMN_ANSWER_TEXT)));
		return radioButtonAnswer;
	}

	private SpinnerAnswer cursorToSpinnerAnswer(Cursor cursor) {
		SpinnerAnswer spinnerAnswer = new SpinnerAnswer();
		spinnerAnswer.setAnswerId(cursor.getLong(cursor.getColumnIndex(SpinnerAnswersTable.COLUMN_ANSWER_ID)));
		spinnerAnswer.setAnswerTypeId(Answer.SPINNER_TYPE);
		spinnerAnswer.setAnswer(cursor.getString(cursor.getColumnIndex(SpinnerAnswersTable.COLUMN_ANSWER_TEXT)));
		return spinnerAnswer;
	}

	private TextAnswer cursorToTextAnswer(Cursor cursor) {
		TextAnswer textAnswer = new TextAnswer();
		textAnswer.setAnswerId(cursor.getLong(cursor.getColumnIndex(TextAnswersTable.COLUMN_ANSWER_ID)));
		textAnswer.setAnswerTypeId(Answer.TEXT_TYPE);
		textAnswer.setAnswerValue(cursor.getString(cursor.getColumnIndex(TextAnswersTable.COLUMN_ANSWER_TEXT)));
		return textAnswer;
	}

	// Delete customer
	/**
	 * Pass the customer object that you would like to have deleted from the local database.
	 * 
	 * @param customer Object
	 */
	public void deleteCustomer(Customer customer){
		// open the database
		// FIXME when deleting a customer all other items for a customer need to be deleted. 
		
		String whereClause = " = " + customer.getCustomerId();
		database.beginTransaction();
		try{
			// Delete the customer, Answers, Potentials, Buildings, Addresses, Items
			database.delete(CustomerTable.TABLE_CUSTOMER, CustomerTable.COLUMN_ID + whereClause, null);
			database.delete(AnswerTable.TABLE_ANSWERS, AnswerTable.COLUMN_CUSTOMER_ID + whereClause, null);
			database.delete(PotentialTable.TABLE_POTENTIAL, PotentialTable.COLUMN_CUSTOMER_ID + whereClause, null);
			database.delete(BuildingTable.TABLE_BUILDING, BuildingTable.COLUMN_CUSTOMERID + whereClause, null);
			database.setTransactionSuccessful();
		} catch (SQLException e){
			e.printStackTrace();
			Log.e(TAG, "Unable to delete the customer");
		} finally {
			database.endTransaction();
		}
		
		
	}

	public void deleteItem(long itemId) {
		// Delete the specific item from the database. 
		// Start transaction in case something goes wrong
		database.beginTransaction();
		try{
			// Delete Individual Answers
			String query = "SELECT * FROM "
					+ AnswerTable.TABLE_ANSWERS
					+ " WHERE " 
					+ AnswerTable.COLUMN_ITEM_ID
					+ " = "
					+ itemId;
			Log.d(TAG, query);
			Cursor cursor = database.rawQuery(query, null);
			for(cursor.moveToFirst(); cursor.isAfterLast() != true; cursor.moveToNext()){
				String whereClause = null;
				switch(cursor.getInt(cursor.getColumnIndex(AnswerTable.COLUMN_ANSWER_TYPE))){
				case Answer.AUTOCOMPLETE_TYPE:
					// Delete all autocomplete answer
					whereClause = AutoCompleteAnswersTable.COLUMN_ANSWER_ID + " = " + cursor.getInt(cursor.getColumnIndex(AnswerTable.COLUMN_ID));
					database.delete(AutoCompleteAnswersTable.TABLE_AUTOCOMPLETE_ANSWERS, whereClause, null);
					break;
				case Answer.BOOLEAN_TYPE:
					// Delete all boolean answers
					whereClause = BooleanAnswersTable.COLUMN_ANSWER_ID + " = " + cursor.getInt(cursor.getColumnIndex(AnswerTable.COLUMN_ID));
					database.delete(BooleanAnswersTable.TABLE_BOOLEAN_ANSWERS, whereClause, null);
					break;
				case Answer.PHOTO_TYPE:
					// Delete all photo answers
					whereClause = BooleanAnswersTable.COLUMN_ANSWER_ID + " = " + cursor.getInt(cursor.getColumnIndex(AnswerTable.COLUMN_ID));
					database.delete(BooleanAnswersTable.TABLE_BOOLEAN_ANSWERS, whereClause, null);
					break;
				case Answer.RADIOBUTTON_TYPE:
					// Delete all radiobutton answers
					whereClause = RadioButtonAnswersTable.COLUMN_ANSWER_ID + " = " + cursor.getInt(cursor.getColumnIndex(AnswerTable.COLUMN_ID));
					database.delete(RadioButtonAnswersTable.TABLE_RADIOBUTTON_ANSWERS, whereClause, null);
					break;
				case Answer.SPINNER_TYPE:
					// Delete all spinner answers
					whereClause = SpinnerAnswersTable.COLUMN_ANSWER_ID + " = " + cursor.getInt(cursor.getColumnIndex(AnswerTable.COLUMN_ID));
					database.delete(SpinnerAnswersTable.TABLE_SPINNER_ANSWERS, whereClause, null);
					break;
				case Answer.TEXT_TYPE:
					// Delete all text answers
					whereClause = TextAnswersTable.COLUMN_ANSWER_ID + " = " + cursor.getInt(cursor.getColumnIndex(AnswerTable.COLUMN_ID));
					database.delete(TextAnswersTable.TABLE_TEXT_ANSWERS, whereClause, null);
					break;
				}
			}
			// Delete from the Answer table
			database.delete(AnswerTable.TABLE_ANSWERS, AnswerTable.COLUMN_ITEM_ID + " = " + itemId, null);
			// Delete from the item table
			database.delete(ItemTable.TABLE_ITEM, ItemTable.COLUMN_ID + " = " + itemId, null);
			// If it gets here the transaction will set successful.
			database.setTransactionSuccessful();
		} catch (SQLException e){
			Log.e(TAG, "Unable to delete the item from the database. An Error has occured.");
			e.printStackTrace();
		} finally {
			database.endTransaction();
		}
		
	}

	public Address getAddress(long addressId) {
		// Retreieve an address by id
		Address address = new Address();
		String query = "SELECT * FROM "
				+ AddressTable.TABLE_ADDRESS
				+ " WHERE " 
				+ AddressTable.COLUMN_ID
				+ " = "
				+ addressId;
		try {
			Cursor cursor;
			Log.d(TAG, query);
			cursor = database.rawQuery(query , null);
			cursor.moveToFirst();
			address = cursorToAddress(cursor);
			cursor.close();
		} catch (Exception e){
			Log.e(TAG, "Error occured " + e);
			Log.e(TAG, "Unable to retrieve address from the database");
		}
		return address;
	}

	public Answer getAnswer(long itemId, long questionId, int AnswerTypeId) {
		// TODO Get the answers for the item and the question
		Answer answer = null;
		
		switch(AnswerTypeId){
		case(Answer.AUTOCOMPLETE_TYPE):
			// Try to get the answer
			try {
				String query = "SELECT * FROM " 
						+ AutoCompleteAnswersTable.TABLE_AUTOCOMPLETE_ANSWERS
						+ " INNER JOIN "
						+ AnswerTable.TABLE_ANSWERS
						+ " ON "
						+ AutoCompleteAnswersTable.TABLE_AUTOCOMPLETE_ANSWERS + "." + AutoCompleteAnswersTable.COLUMN_ANSWER_ID
						+ " = "
						+ AnswerTable.TABLE_ANSWERS + "." + AnswerTable.COLUMN_ID
						+ " WHERE "
						+ AnswerTable.TABLE_ANSWERS + "." + AnswerTable.COLUMN_QUESTION_ID
						+ " = " 
						+ questionId
						+ " AND "
						+ AnswerTable.TABLE_ANSWERS + "." + AnswerTable.COLUMN_ITEM_ID
						+ " = "
						+ itemId;
				
				Cursor cursor;
				Log.d(TAG, query);
				cursor = database.rawQuery(query, null);
				cursor.moveToFirst();
				answer = cursorToAutoCompleteAnswer(cursor);
				cursor.close();
			} catch (SQLException e) {
				Log.e(TAG, "Unable to retrieve autocomplete answer");
				e.printStackTrace();
			}
			break;
		case(Answer.BOOLEAN_TYPE):
			// Try to get the answer
			try {
				String query = "SELECT * FROM " 
						+ BooleanAnswersTable.TABLE_BOOLEAN_ANSWERS
						+ " INNER JOIN "
						+ AnswerTable.TABLE_ANSWERS
						+ " ON "
						+ BooleanAnswersTable.TABLE_BOOLEAN_ANSWERS + "." + BooleanAnswersTable.COLUMN_ANSWER_ID
						+ " = "
						+ AnswerTable.TABLE_ANSWERS + "." + AnswerTable.COLUMN_ID
						+ " WHERE "
						+ AnswerTable.TABLE_ANSWERS + "." + AnswerTable.COLUMN_QUESTION_ID
						+ " = " 
						+ questionId
						+ " AND "
						+ AnswerTable.TABLE_ANSWERS + "." + AnswerTable.COLUMN_ITEM_ID
						+ " = "
						+ itemId;
				
				Cursor cursor;
				Log.d(TAG, query);
				cursor = database.rawQuery(query, null);
				cursor.moveToFirst();
				answer = cursorToBooleanAnswer(cursor);
				cursor.close();
			} catch (SQLException e) {
				Log.e(TAG, "Unable to retrieve boolean answer");
				e.printStackTrace();
			}
			break;
		case(Answer.PHOTO_TYPE):
			// Try to get the answer
			try {
				String query = "SELECT * FROM " 
					+ PhotoAnswerTable.TABLE_PHOTO_ANSWERS
					+ " INNER JOIN "
					+ AnswerTable.TABLE_ANSWERS
					+ " ON "
					+ PhotoAnswerTable.TABLE_PHOTO_ANSWERS + "." + PhotoAnswerTable.COLUMN_ANSWER_ID
					+ " = "
					+ AnswerTable.TABLE_ANSWERS + "." + AnswerTable.COLUMN_ID
					+ " WHERE "
					+ AnswerTable.TABLE_ANSWERS + "." + AnswerTable.COLUMN_QUESTION_ID
					+ " = " 
					+ questionId
					+ " AND "
					+ AnswerTable.TABLE_ANSWERS + "." + AnswerTable.COLUMN_ITEM_ID
					+ " = "
					+ itemId;
						
				Cursor cursor;
				Log.d(TAG, query);
				cursor = database.rawQuery(query, null);
				cursor.moveToFirst();
				answer = cursorToPhotoAnswer(cursor);
				cursor.close();
			} catch (SQLException e) {
				Log.e(TAG, "Unable to retrieve photo answer");
				e.printStackTrace();
			}
			break;
		case(Answer.RADIOBUTTON_TYPE):
			// Try to get the answer
			try {
				String query = "SELECT * FROM " 
					+ RadioButtonAnswersTable.TABLE_RADIOBUTTON_ANSWERS
					+ " INNER JOIN "
					+ AnswerTable.TABLE_ANSWERS
					+ " ON "
					+ RadioButtonAnswersTable.TABLE_RADIOBUTTON_ANSWERS + "." + RadioButtonAnswersTable.COLUMN_ANSWER_ID
					+ " = "
					+ AnswerTable.TABLE_ANSWERS + "." + AnswerTable.COLUMN_ID
					+ " WHERE "
					+ AnswerTable.TABLE_ANSWERS + "." + AnswerTable.COLUMN_QUESTION_ID
					+ " = " 
					+ questionId
					+ " AND "
					+ AnswerTable.TABLE_ANSWERS + "." + AnswerTable.COLUMN_ITEM_ID
					+ " = "
					+ itemId;
						
				Cursor cursor;
				Log.d(TAG, query);
				cursor = database.rawQuery(query, null);
				cursor.moveToFirst();
				answer = cursorToRadioButtonAnswer(cursor);
				cursor.close();
			} catch (SQLException e) {
				Log.e(TAG, "Unable to retrieve photo answer");
				e.printStackTrace();
			}
			break;
		case(Answer.SPINNER_TYPE):
			// Try to get the answer
			try {
				String query = "SELECT * FROM " 
					+ SpinnerAnswersTable.TABLE_SPINNER_ANSWERS
					+ " INNER JOIN "
					+ AnswerTable.TABLE_ANSWERS
					+ " ON "
					+ SpinnerAnswersTable.TABLE_SPINNER_ANSWERS + "." + SpinnerAnswersTable.COLUMN_ANSWER_ID
					+ " = "
					+ AnswerTable.TABLE_ANSWERS + "." + AnswerTable.COLUMN_ID
					+ " WHERE "
					+ AnswerTable.TABLE_ANSWERS + "." + AnswerTable.COLUMN_QUESTION_ID
					+ " = " 
					+ questionId
					+ " AND "
					+ AnswerTable.TABLE_ANSWERS + "." + AnswerTable.COLUMN_ITEM_ID
					+ " = "
					+ itemId;
						
				Cursor cursor;
				Log.d(TAG, query);
				cursor = database.rawQuery(query, null);
				cursor.moveToFirst();
				answer = cursorToSpinnerAnswer(cursor);
				cursor.close();
			} catch (SQLException e) {
				Log.e(TAG, "Unable to retrieve photo answer");
				e.printStackTrace();
			}
			break;
		case(Answer.TEXT_TYPE):
			// Try to get the answer
			try {
				String query = "SELECT * FROM " 
					+ TextAnswersTable.TABLE_TEXT_ANSWERS
					+ " INNER JOIN "
					+ AnswerTable.TABLE_ANSWERS
					+ " ON "
					+ TextAnswersTable.TABLE_TEXT_ANSWERS + "." + TextAnswersTable.COLUMN_ANSWER_ID
					+ " = "
					+ AnswerTable.TABLE_ANSWERS + "." + AnswerTable.COLUMN_ID
					+ " WHERE "
					+ AnswerTable.TABLE_ANSWERS + "." + AnswerTable.COLUMN_QUESTION_ID
					+ " = " 
					+ questionId
					+ " AND "
					+ AnswerTable.TABLE_ANSWERS + "." + AnswerTable.COLUMN_ITEM_ID
					+ " = "
					+ itemId;
						
				Cursor cursor;
				Log.d(TAG, query);
				cursor = database.rawQuery(query, null);
				cursor.moveToFirst();
				answer = cursorToTextAnswer(cursor);
				cursor.close();
			} catch (SQLException e) {
				Log.e(TAG, "Unable to retrieve photo answer");
				e.printStackTrace();
			}
			break;
		default:
			break;
		}
		return answer;
	}
	private Answer getAutoCompleteAnswer(Long questionId) {
		// TODO Retrieve the AutoCompleteQuestion
		AutoCompleteAnswer autoCompleteQuestion = new AutoCompleteAnswer(questionId);
		// Open the database connection
		
		String query = "SELECT * FROM "
				+ AutoCompleteQuestionTable.TABLE_AUTO_COMPLETE_QUESTIONS
				+ " WHERE "
				+ AutoCompleteQuestionTable.COLUMN_QUESTION_ID
				+ " = "
				+ questionId
				+";";
		try{
			Cursor cursor;
			Log.d(TAG, query);
			cursor = database.rawQuery(query, null);
			cursor.moveToFirst();
			autoCompleteQuestion = cursorToAutoCompleteQuestion(cursor);
			cursor.close();
		} catch (SQLException e){
			e.printStackTrace();
		}
		autoCompleteQuestion.setAnswerTypeId(Answer.AUTOCOMPLETE_TYPE);
		return autoCompleteQuestion;
	}
	public BooleanAnswer getBooleanAnswer(long answerId) {
		// TODO return a BooleanAnswer from the databse
		BooleanAnswer booleanAnswer = new BooleanAnswer();
		String query = "SELECT * FROM "
				+ BooleanAnswersTable.TABLE_BOOLEAN_ANSWERS
				+ " WHERE "
				+ BooleanAnswersTable.COLUMN_ANSWER_ID
				+ " = "
				+ answerId;
		
		try{
			Cursor cursor;
			Log.d(TAG, query);
			cursor = database.rawQuery(query, null);
			cursor.moveToFirst();
			booleanAnswer = cursorToBooleanAnswer(cursor);
			cursor.close();
		} catch (SQLException e){
			e.printStackTrace();
			Log.e(TAG, "Unable to retrieve the boolean answer");
		}
		return booleanAnswer;
	}

	private Answer getBooleanTypeAnswer(Long questionId) {
		
		BooleanAnswer booleanAnswer = new BooleanAnswer();
		try {
			String query = "Select * From " + BooleanQuestionTable.TABLE_BOOLEAN_QUESTIONS + " WHERE " + BooleanQuestionTable.COLUMN_QUESTION_ID + " = " + questionId;
			Log.d(TAG, query);
			Cursor cursor;
			cursor = database.rawQuery(query, null);
			cursor.moveToFirst();
			booleanAnswer.setTrueText(cursor.getString(cursor.getColumnIndex(BooleanQuestionTable.COLUMN_TRUE_TEXT)));
			booleanAnswer.setFalseText(cursor.getString(cursor.getColumnIndex(BooleanQuestionTable.COLUMN_FALSE_TEXT)));
			booleanAnswer.setValue(false);
			cursor.close();
		} catch (Exception e) {
			e.printStackTrace();
			Log.e(TAG, "Unable to create custom true/false text. Using standard True/False");
			booleanAnswer.setTrueText("true");
			booleanAnswer.setFalseText("false");
		}
		return booleanAnswer;
	}
	public Building getBuilding(long id) {
		// TODO Auto-generated method stub
		Building building = new Building();
		String query = "SELECT * FROM "
				+ BuildingTable.TABLE_BUILDING
				+ " WHERE "
				+ BuildingTable.COLUMN_ID
				+ " = "
				+ id;
		
		try{
			// Lookup the building
			Cursor cursor;
			cursor = database.rawQuery(query, null);
			cursor.moveToFirst();
			building = cursorToBuilding(cursor);
			cursor.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return building;
	}
	// Read Customer
	/**
	 * Load customer from database referencing the customerId.
	 * 
	 * @param id customerId from the database record
	 * @return Customer Object
	 */
	public Customer getCustomer(long id){
		// Open the database connection
		
		Log.d(TAG, "running getCustomer looking up customer id = " + id);
		Customer customer = new Customer();
		Cursor cursor;
		// Look up form the customer table the specific customer
		try {
			String query = "SELECT * FROM "
					+ CustomerTable.TABLE_CUSTOMER
					+ " WHERE "
					+ CustomerTable.COLUMN_ID
					+ " = "
					+ id;
			Log.d(TAG, query);
			cursor = database.rawQuery(query, null);
			cursor.moveToFirst();
			customer = cursorToCustomer(cursor);
			cursor.close();
		} catch (Exception e) {
			Log.e(TAG, "Unable to retrieve customer id = "+id);
		}
		// Look up all of the buildings that the customer has associated with them. 
		customer.setBuildingList(getCustomerBuildings(customer.getCustomerId()));
		// Look up the potentials that are associated with this customer from the potentials table. 
		customer.setPotentialList(getCustomerPotentials(customer.getCustomerId()));
		// Close database and return results
		
		return customer;
		
	}
	public ArrayList<Address> getCustomerAddresses(long customerId) {
		
		ArrayList<Address> uniqueCustomerAddresses = new ArrayList<Address>();
		String query = "SELECT "
				+ AddressTable.TABLE_ADDRESS +"."+ AddressTable.COLUMN_ID + ", "
				+ AddressTable.TABLE_ADDRESS +"."+ AddressTable.COLUMN_STREET + ", "
				+ AddressTable.TABLE_ADDRESS +"."+ AddressTable.COLUMN_CITY + ", "
				+ AddressTable.TABLE_ADDRESS +"."+ AddressTable.COLUMN_STATE + ", "
				+ AddressTable.TABLE_ADDRESS +"."+ AddressTable.COLUMN_COUNTRY + ", "
				+ AddressTable.TABLE_ADDRESS +"."+ AddressTable.COLUMN_ZIPCODE + ", "
				+ AddressTable.TABLE_ADDRESS +"."+ AddressTable.COLUMN_CREATED 
				+ " FROM " + AddressTable.TABLE_ADDRESS
				+ " INNER JOIN " + BuildingTable.TABLE_BUILDING 
				+ " ON "
				+ AddressTable.TABLE_ADDRESS+"."+AddressTable.COLUMN_ID
				+ " = " 
				+ BuildingTable.TABLE_BUILDING+"."+BuildingTable.COLUMN_ADDRESSID
				+ " WHERE " 
				+ BuildingTable.TABLE_BUILDING+"."+BuildingTable.COLUMN_CUSTOMERID + " = " + customerId
				+ ";";
		Cursor cursor;
		// Fill the cursor
		Log.d(TAG, query);
		cursor = database.rawQuery(query, null);
		cursor.moveToFirst();
		while(!cursor.isAfterLast()){
			uniqueCustomerAddresses.add(cursorToAddress(cursor));
			cursor.moveToNext();
		}
		cursor.close();
		
		return uniqueCustomerAddresses;
	}
	public ArrayList<Building> getCustomerBuildings(long customerId) {
		ArrayList<Building> buildings = new ArrayList<Building>();
		try{
			Cursor cursor;
			String query = "SELECT * FROM " 
					+ BuildingTable.TABLE_BUILDING 
					+ " WHERE "
					+ BuildingTable.COLUMN_CUSTOMERID 
					+ " = " 
					+ customerId;
			Log.d(TAG, query);
			cursor = database.rawQuery(query, null);
			for(cursor.moveToFirst(); cursor.isAfterLast() != true; cursor.moveToNext()){
				Building building = new Building();
				building = cursorToBuilding(cursor);
				buildings.add(building);
			}
		} catch (Exception e){
			e.printStackTrace();
		}
		return buildings;
	}
	// Get all of the customers from the database
	/**
	 * Return a List of All Customers Objects from the database.
	 * 
	 * @return List<Customer>
	 */
	public List<Customer> getCustomerList(){
		List<Customer> customers = new ArrayList<Customer>();
		// Open the database connection
		
		try {
			// Build the query
			String query = "SELECT * FROM "
					+ CustomerTable.TABLE_CUSTOMER;
			Cursor cursor;
			cursor = database.rawQuery(query, null);
			for(cursor.moveToFirst(); cursor.isAfterLast() != true; cursor.moveToNext()){
				Customer customer = new Customer();
				customer = cursorToCustomer(cursor);
				customers.add(customer);
			}
			// Close the cursor when finished
			cursor.close();
		} catch (Exception e) {
			Log.e(TAG, "Unable to retrieve a list of customers from the database.");
			e.printStackTrace();
		}
		// Close the database
		
		return customers;
	}

	public ArrayList<Potential> getCustomerPotentials(long customerId) {
		// TODO Auto-generated method stub
		ArrayList<Potential> potentials = new ArrayList<Potential>();
		// Open the database connection
		String query = "SELECT * FROM " 
				+ PotentialTable.TABLE_POTENTIAL
				+ " WHERE "
				+ PotentialTable.COLUMN_CUSTOMER_ID
				+ " = "
				+ customerId;
		// Query the database
		try{
			Cursor cursor;
			Log.d(TAG, query);
			cursor = database.rawQuery(query, null);
			for(cursor.moveToFirst(); cursor.isAfterLast() != true; cursor.moveToNext()){
				Potential potential = cursorToPotential(cursor);
				potential.setItemCount(getPotentialItemCount(potential));
				potentials.add(potential);
			}
		} catch (SQLException e){
			e.printStackTrace();
			Log.e(TAG, "Unable to get potentials");
		}
		
		
		return potentials;
	}

	public Item getItem(long itemId) {
		// TODO Create an item with all answers from the past
		Item item = new Item();
		String query = "SELECT * FROM "
				+ ItemTable.TABLE_ITEM
				+ " WHERE "
				+ ItemTable.COLUMN_ID + " = " + itemId;
		try{
			Cursor cursor;
			cursor = database.rawQuery(query, null);
			cursor.moveToFirst();
			item = cursorToItem(cursor);
		} catch  (SQLException e){
			e.printStackTrace();
			Log.e(TAG, "Unable to retrieve the item");
		}
		return item;
	}


	public ArrayList<Question> getItemQuestions(ItemType itemType2) {
		// Open the database
		
		ArrayList<Question> questions = new ArrayList<Question>();
		Cursor cursor;
		String query = "SELECT * FROM " + QuestionTable.TABLE_QUESTION + " WHERE "+ QuestionTable.COLUMN_ITEM_TYPE_ID + " = " + itemType2.getItemTypeId();
		// Load the cursor
		Log.d(TAG,query);
		cursor = this.database.rawQuery(query, null);
		// Fill the list
		for(cursor.moveToFirst(); cursor.isAfterLast() != true; cursor.moveToNext()){
			questions.add(cursorToQuestion(cursor));
		}
		// Close the cursor
		cursor.close();
		return questions;
	}
	public ItemType getItemType(Long itemTypeId) {
		// Open the database
		
		ItemType itemType = new ItemType();
		// Lookup in the database where the string extra name matches
		String query = "SELECT * FROM "
				+ ItemTypeTable.TABLE_ITEM_TYPE 
				+ " WHERE "
				+ ItemTypeTable.COLUMN_ID
				+ " = '" + itemTypeId + "'";
		Log.d(TAG, query);
		Cursor cursor;
		cursor = database.rawQuery(query, null);
		cursor.moveToFirst();
		itemType = cursorToItemType(cursor);
		// Close the database
		cursor.close();
		return itemType;
	}

	private ItemType getItemTypeById(long id) {
		ItemType itemType = new ItemType();
		// Open the database connection
		
		// Create the query 
		String query = "SELECT * FROM "
				+ ItemTypeTable.TABLE_ITEM_TYPE
				+ " WHERE "
				+ ItemTypeTable.COLUMN_ID
				+ " = "
				+ id;
		// Query the database
		try{
			// Create the cursor
			Cursor cursor;
			cursor = database.rawQuery(query, null);
			cursor.moveToFirst();
			itemType = cursorToItemType(cursor);
			cursor.close();
		} catch (Exception e){
			Log.e(TAG, "Unable to retrieve item type from the database");
		}
		
		return itemType;
		
	}

	public ArrayList<ItemType> getItemTypes() {
		// TODO Auto-generated method stub
		ArrayList<ItemType> itemTypes = new ArrayList<ItemType>();
		String query = "SELECT * FROM "
				+ ItemTypeTable.TABLE_ITEM_TYPE;
		try{
			Log.d(TAG, query);
			Cursor cursor;
			cursor = database.rawQuery(query, null);
			for(cursor.moveToFirst(); cursor.isAfterLast() != true; cursor.moveToNext()){
				itemTypes.add(cursorToItemType(cursor));
			}
			cursor.close();
		} catch (SQLException e){
			Log.e(TAG, "Unable to retrieve the list of item types");
			e.printStackTrace();
		}
		return itemTypes;
	}

	private Answer getPhotoAnswer(Long questionId) {
		// TODO Create photo answer from the database.
		PhotoAnswer photoAnswer = new PhotoAnswer();
		// Open the database connection
		
		// Create Query
		String query = "SELECT * FROM "
				+ PhotoQuestionTable.TABLE_PHOTO_QUESTIONS
				+ " WHERE " 
				+ PhotoQuestionTable.COLUMN_QUESTION_ID
				+ " = "
				+ questionId
				+";";
		// Create the cursor to contain the result
		
		try{
			Cursor cursor;
			Log.d(TAG, query);
			cursor = database.rawQuery(query, null);
			cursor.moveToFirst();
			photoAnswer = cursorToPhotoQuestion(cursor);
			cursor.close();
		} catch (SQLException e){
			e.printStackTrace();
			Log.e(TAG, "Unable to get photo answer");
		}
		
		return photoAnswer;
	}

	private Answer getPickListAnswer(Long questionId) {
		
		SpinnerAnswer pickListAnswer = new SpinnerAnswer();
		try {
			String query = "SELECT * FROM " + SpinnerQuestionTable.TABLE_SPINNER_QUESTION + " WHERE " + SpinnerQuestionTable.COLUMN_QUESTION_ID + " = " + questionId;
			Cursor cursor; 
			Log.d(TAG, query);
			cursor = database.rawQuery(query, null);
			// FIXME iterate though cursor using for loop
			cursor.moveToFirst();
			while(!cursor.isAfterLast()){
				String option = cursor.getString(cursor.getColumnIndex(SpinnerQuestionTable.COLUMN_OPTION_TEXT));
				Log.d(TAG, "Adding " + option);
				pickListAnswer.getPossibleAnswers().add(option);
				cursor.moveToNext();
			}
			cursor.close();
		} catch (Exception e) {
			e.printStackTrace();
			Log.e(TAG, "Unable to retrieve spinner options for the picklist");
		}
		
		return pickListAnswer;
	}

	public Potential getPotential(long potentialId) {
		// Open Database
		
		// Create the potential to return
		Potential potential = new Potential();
		// Build the query
		String query = "SELECT * FROM "
				+ PotentialTable.TABLE_POTENTIAL
				+ " WHERE "
				+ PotentialTable.COLUMN_ID + " = " + potentialId
				+ ";";
		// Query the database
		try {
			Cursor cursor;
			Log.d(TAG, query);
			cursor = database.rawQuery(query, null);
			cursor.moveToFirst();
			potential = cursorToPotential(cursor);
			cursor.close();
		} catch (Exception e) {
			Log.e(TAG, "Unable to retrieve potential form the database");
		}
		// Create a potential from the cursor object. 
		
		return potential;
	}

	private int getPotentialItemCount(Potential potential) {
		// TODO return the count of items in the potential
		String query = "SELECT COUNT(*) FROM "
				+ ItemTable.TABLE_ITEM
				+ " WHERE "
				+ ItemTable.COLUMN_POTENTIAL_ID
				+ " = "
				+ potential.getId();
		try{
			Cursor cursor;
			Log.d(TAG, query);
			cursor = database.rawQuery(query, null);
			cursor.moveToFirst();
			int totalItems = cursor.getInt(0);
			cursor.close();
			return totalItems;
		} catch (SQLException e) {
			e.printStackTrace();
			Log.e(TAG, "No Items are associated with this potential");
			return 0;
		}
	}

	public ArrayList<Item> getPotentialItems(long id) {
		ArrayList<Item> potentialItems = new ArrayList<Item>();
		// Open the database
		
		// Query
		String query = "SELECT * FROM " 
				+ ItemTable.TABLE_ITEM
				+ " WHERE " 
				+ ItemTable.COLUMN_POTENTIAL_ID
				+ " = "
				+ id
				+ ";";
		// Create the cursor to read the results to
		Cursor cursor;
		// Query the database
		try{
			Log.d(TAG, query);
			cursor = database.rawQuery(query, null);
			for(cursor.moveToFirst(); cursor.isAfterLast() != true; cursor.moveToNext()){
				Log.d(TAG, "Added Item to Potential");
				potentialItems.add(cursorToItem(cursor));
			}
			cursor.close();
		} catch (Exception e){
			Log.e(TAG, "Unable to retrieve any items for the given potential.");
			Log.e(TAG, e.toString());
		}
		
		return potentialItems;
	}

	public ArrayList<Potential> getPotentials(){
		// return all of the potentials
		ArrayList<Potential> potentials = new ArrayList<Potential>();
		String query = "SELECT * FROM "
				+ PotentialTable.TABLE_POTENTIAL;
		try{
			Cursor cursor;
			cursor = database.rawQuery(query, null);
			for(cursor.moveToFirst(); cursor.isAfterLast() != true; cursor.moveToNext()){
				Potential potential = cursorToPotential(cursor);
				potential.setItemCount(getPotentialItemCount(potential));
				potentials.add(potential);
			}
			cursor.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return potentials;
	}

	private Answer getRadioButtonAnswer(Long questionId) {
		
		Log.d(TAG, "Looking up Radio Button Type");
		RadioButtonAnswer radioButtonAnswer = new RadioButtonAnswer();
		String query = "SELECT * FROM " + RadioButtonQuestionTable.TABLE_RADIOBUTTONQUESTIONS + " WHERE " + RadioButtonQuestionTable.COLUMN_QUESTION_ID + " = " + questionId;
		try {
			Cursor cursor;
			Log.d(TAG, query);
			cursor = database.rawQuery(query, null);
			// FIXME iterate though cursor using the for loop
			cursor.moveToFirst();
			while(!cursor.isAfterLast()){
				// Add the option to the radio button
				radioButtonAnswer.getAnswerOptions().add(cursor.getString(cursor.getColumnIndex(RadioButtonQuestionTable.COLUMN_OPTION_TEXT)));
				cursor.moveToNext();
			}
			cursor.close();
		} catch (Exception e) {
			e.printStackTrace();
			Log.e(TAG, "No radio button answer entries exisit for this question");
		}
		
		return radioButtonAnswer;
	}

	public ArrayList<String> getRecentAnswerForCustomer(long customerId,
			Long questionId) {
		ArrayList<String> recentAnswers = new ArrayList<String>();
		String query = "SELECT DISTINCT "+ AutoCompleteAnswersTable.COLUMN_ANSWER_TEXT +" FROM " 
		+ AutoCompleteAnswersTable.TABLE_AUTOCOMPLETE_ANSWERS 
		+ " INNER JOIN "
		+ AnswerTable.TABLE_ANSWERS
		+ " ON "
		+ AutoCompleteAnswersTable.TABLE_AUTOCOMPLETE_ANSWERS+"."+AutoCompleteAnswersTable.COLUMN_ANSWER_ID
		+ " = "
		+ AnswerTable.TABLE_ANSWERS + "." + AnswerTable.COLUMN_ID
		+ " AND "
		+ AnswerTable.TABLE_ANSWERS+"."+AnswerTable.COLUMN_QUESTION_ID  + " = " + questionId
		+ " AND "
		+ AnswerTable.TABLE_ANSWERS+"."+AnswerTable.COLUMN_CUSTOMER_ID + " = "  + customerId;
		
		// Open the database
		
		Cursor cursor;
		Log.d(TAG, query);
		try {
			cursor = database.rawQuery(query, null);
			if(cursor.getCount() > 0){
				for(cursor.moveToFirst(); cursor.isAfterLast() != true; cursor.moveToNext()){
					recentAnswers.add(cursor.getString(cursor.getColumnIndex(TextAnswersTable.COLUMN_ANSWER_TEXT)));
				}
			}
			cursor.close();
		} catch (Exception e) {
			e.printStackTrace();
			Log.e(TAG, "Unable to retrieve a list of recent answers");
		}
		return recentAnswers;
	}

	public SpinnerAnswer getSpinnerAnswer(long answerId) {
		// TODO return a Spinner Answer from the database
		SpinnerAnswer spinnerAnswer = new SpinnerAnswer();
		return spinnerAnswer;
	}

	private Answer getTextAnswer(Long questionId) {
		
		Log.v(TAG, "getTextAnswer()");
		String query = "SELECT * FROM " + TextQuestionTable.TABLE_TEXTQUESTIONS + " WHERE " + TextQuestionTable.COLUMN_QUESTION_ID + " = " + questionId;
		Log.v(TAG, query);
		TextAnswer textAnswer = new TextAnswer();
		try {
			Cursor cursor;
			cursor = database.rawQuery(query, null);				
			cursor.moveToFirst();
			textAnswer.setHintText(cursor.getString(cursor.getColumnIndex(TextQuestionTable.COLUMN_HINT_TEXT)));
			textAnswer.setValueType(cursor.getInt(cursor.getColumnIndex(TextQuestionTable.COLUMN_VALUE_TYPE)));
		} catch (Exception e) {
			e.printStackTrace();
			Log.e(TAG ,"Unable to get text answer details");
		}
		
		return textAnswer;
	}

	public void importSheetsFromFile(FileInputStream fileInputStream) {
		// TODO Import CSV File from email to import the sheets. 
		Workbook workbook = null;
		try {
			workbook = Workbook.getWorkbook(fileInputStream);
		} catch (BiffException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		} catch (IOException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}		
		database.beginTransaction();
		try {
			// FIXME Delete all previous items and forms. 
			// Set all other forms inactive for new walkthrus
			database.delete(AnswerTable.TABLE_ANSWERS, null, null);
			database.delete(AutoCompleteAnswersTable.TABLE_AUTOCOMPLETE_ANSWERS,null,null);
			database.delete(AutoCompleteQuestionTable.TABLE_AUTO_COMPLETE_QUESTIONS, null, null);
			database.delete(BooleanAnswersTable.TABLE_BOOLEAN_ANSWERS, null, null);
			database.delete(ItemTable.TABLE_ITEM, null, null);
			database.delete(ItemTypeTable.TABLE_ITEM_TYPE,null,null);
			database.delete(PhotoAnswerTable.TABLE_PHOTO_ANSWERS, null, null);
			database.delete(PhotoQuestionTable.TABLE_PHOTO_QUESTIONS, null, null);
			database.delete(PotentialTable.TABLE_POTENTIAL, null, null);
			database.delete(QuestionTable.TABLE_QUESTION, null, null);
			database.delete(RadioButtonAnswersTable.TABLE_RADIOBUTTON_ANSWERS, null, null);
			database.delete(RadioButtonQuestionTable.TABLE_RADIOBUTTONQUESTIONS, null, null);
			database.delete(SpinnerAnswersTable.TABLE_SPINNER_ANSWERS, null, null);
			database.delete(SpinnerQuestionTable.TABLE_SPINNER_QUESTION, null, null);
			database.delete(TextAnswersTable.TABLE_TEXT_ANSWERS, null, null);
			database.delete(TextQuestionTable.TABLE_TEXTQUESTIONS, null, null);
			ContentValues values = new ContentValues();
			// First create the item type
			values.clear();
			// Import the full worksheet
			Sheet sheets[] = workbook.getSheets();
			Log.d(TAG, "Total Number of Sheets = " + sheets.length);
			for(Sheet thisSheet : sheets){
				// Name of the sheet is equal to an Item type
				Log.d(TAG, "Sheet name to add as new item = " + thisSheet.getName());
				if(thisSheet.getName().equals("DV-IDENTITY-0")){
					break;
				}
				values.put(ItemTypeTable.COLUMN_NAME, thisSheet.getName());
				// TODO Insert the item into the database. If item name exists increment version
				//				String query = "SELECT * FROM "
				//						+ ItemTypeTable.TABLE_ITEM_TYPE
				//						+ " WHERE "
				//						+ ItemTypeTable.COLUMN_NAME
				//						+ " = "
				//						+ thisSheet.getName();
				//				Cursor cursor = database.rawQuery(query, null);
				//				if(cursor.moveToFirst()){
				//					// Set this item to inactive
				//					String
				//				}
				long itemTypeId = database.insert(ItemTypeTable.TABLE_ITEM_TYPE, null, values);
				for(int r = 1; r < thisSheet.getRows(); r++){
					// Question Text (column/row)
					Log.v(TAG, "Processing Row = " + r);
					Cell cellA = thisSheet.getCell(0, r);
					if(cellA.getContents().equals("")){
						// Stop reading if it is empty
						Log.d(TAG, "Cell A is Empty Breaking operation");
						break;
					}
					String questionText = cellA.getContents();
					// Question Type
					Cell cellB = thisSheet.getCell(1, r);
					String questionType = cellB.getContents();
					// Question Hint
					Cell cellC = thisSheet.getCell(2,r);
					String questionHint = cellC.getContents();
					// Options...
					ArrayList<String> options = new ArrayList<String>();
					for(int c = 3; c < thisSheet.getColumns(); c++){
						Cell cellOption = thisSheet.getCell(c,r);
						if(!cellOption.getContents().equals("")){
							// Not empty add to the list of options
							options.add(cellOption.getContents());
						} else {
							break;
						}

					}					
					// Add this question to the database transaction then go to the next row.
					if(questionType.equals("Text")){
						Log.d(TAG, "Creating Text Question: " + questionText);
						createTextQuestion(itemTypeId, questionText, 0, questionHint, android.text.InputType.TYPE_CLASS_TEXT);		
					}
					if(questionType.equals("TextNumber")){
						Log.d(TAG, "Creating Number with Text Question: " + questionText);
						createTextQuestion(itemTypeId, questionText, 0, questionHint, android.text.InputType.TYPE_NUMBER_VARIATION_NORMAL);
					}
					if(questionType.equals("Number")){
						Log.d(TAG, "Creating Number (Text)Question: " + questionText);
						createTextQuestion(itemTypeId, questionText, 0, questionHint, android.text.InputType.TYPE_CLASS_NUMBER + android.text.InputType.TYPE_NUMBER_FLAG_DECIMAL);
					}
					if(questionType.equals("AutoComplete")){
						Log.d(TAG, "Creating Auto Complete Question: " + questionText);
						createAutoCompleteQuestion(itemTypeId, questionText, 0, questionHint);
					}
					if(questionType.equals("RadioButton")){
						Log.d(TAG, "Creating Radio Button Question: " + questionText);
						for(String option : options){
							Log.v(TAG, questionText + " Adding Option : " + option);
						}
						createRadioButtonQuestion(itemTypeId, questionText, 0, options);
					}
					if(questionType.equals("Boolean")){
						Log.d(TAG, "Creating Boolean Question: " + questionText);
						for(String option : options){
							Log.v(TAG, questionText + " Adding Option : " + option);
						}
						createBooleanQuestion(itemTypeId, questionText, 0, options.get(0), options.get(1));
					}
					if(questionType.equals("Spinner")){
						Log.d(TAG, "Creating Spinner Question: " + questionText);
						for(String option : options){
							Log.v(TAG, questionText + " Adding Option : " + option);
						}
						createSpinnerQuestion(itemTypeId, questionText, 0, options);
					}
					if(questionType.equals("Photo")){
						Log.d(TAG, "Creating Photo Question: " + questionText);
						createPhotoQuestion(itemTypeId, questionText, 0, questionHint);
					}
				}
			}
			database.setTransactionSuccessful();
		} catch (SQLException e){
			e.printStackTrace();
			Log.e(TAG, "Unable to Create the item in the database, an error has occured.");
			Toast.makeText(this.context, "Error occured importing forms. Please check the format of the Excel file and Try again", Toast.LENGTH_LONG).show();
		} finally {
			try {
				fileInputStream.close();
			} catch (IOException e) {
				// TODO Auto-generated catch block
				Log.e(TAG, "Unable to close the file input stream from imported excel doc");
				e.printStackTrace();
			}
			database.endTransaction();
		}
	}

	// Open the database
	public void Open() throws SQLException {
		database = dbHelper.getWritableDatabase();
	}

	public void updateAutoCompleteAnswer(long itemId, long customerId,
			Long questionId, AutoCompleteAnswer autoCompleteAnswer) {
		// Update the auto complete answer
		try{
			ContentValues values = new ContentValues();
			values.put(AutoCompleteAnswersTable.COLUMN_ANSWER_TEXT, autoCompleteAnswer.getAnswer());
			String whereClause = AutoCompleteAnswersTable.COLUMN_ANSWER_ID + " = " + autoCompleteAnswer.getAnswerId();
			database.update(AutoCompleteAnswersTable.TABLE_AUTOCOMPLETE_ANSWERS, values, whereClause, null);
			Log.v(TAG, "Updated Auto Complete Answer id = " + autoCompleteAnswer.getAnswerId());
		} catch (SQLException e){
			e.printStackTrace();
			Log.e(TAG, "Unable to update the Auto Complete answer id = " + autoCompleteAnswer.getAnswerId());
		}
		
		
	}

	public void updateBooleanAnswer(long itemId, long customerId,
			Long questionId, BooleanAnswer booleanAnswer) {
		// Update a boolean answer type
		try{
			ContentValues values = new ContentValues();
			values.put(BooleanAnswersTable.COLUMN_VALUE, booleanAnswer.getValue());
			values.put(BooleanAnswersTable.COLUMN_ANSWER_TEXT, booleanAnswer.getAnswer());
			String whereClause = BooleanAnswersTable.COLUMN_ANSWER_ID + " = " + booleanAnswer.getAnswerId();
			database.update(BooleanAnswersTable.TABLE_BOOLEAN_ANSWERS, values, whereClause, null);
			Log.v(TAG, "Updated BooleanAnswer ID = " + booleanAnswer.getAnswerId());
		} catch (SQLException e){
			e.printStackTrace();
			Log.e(TAG, "Unable to update the boolean answer");
		}
	}
	public void updateBuilding(Building building) {
		// TODO update the building in the database.
		// Check to see if the address has an ID or if it is a newly created address.
		if(!(building.getAddress().getAddressId() > 0)){
			// Insert the new address into the database. 
			building.setAddress(createAddress(building.getAddress()));
		} else {
			// Update the address information
			updateAddress(building.getAddress());
		}
		String whereClause = BuildingTable.COLUMN_ID + " = " + building.getId();
		ContentValues values = new ContentValues();
		values.put(BuildingTable.COLUMN_GPS_ALTITUDE, building.getpAltitude());
		values.put(BuildingTable.COLUMN_GPS_LATITUDE, building.getpLatitude());
		values.put(BuildingTable.COLUMN_GPS_LONGITUDE, building.getpLongitude());
		values.put(BuildingTable.COLUMN_NAME, building.getName());
		values.put(BuildingTable.COLUMN_CUSTOMERID, building.getCustomerId());
		values.put(BuildingTable.COLUMN_ADDRESSID, building.getAddress().getAddressId());
		try {
			database.update(BuildingTable.TABLE_BUILDING, values, whereClause, null);
			Log.d(TAG, "Building was updated in the database");
		} catch (SQLException e){
			e.printStackTrace();
			Log.e(TAG, "Unable to update the building in the database.");
		}
	}
	public void updateAddress(Address address) {
		// TODO Update this building address
		String whereClause = AddressTable.COLUMN_ID + " = " + address.getAddressId();
		ContentValues values = new ContentValues();
		values.put(AddressTable.COLUMN_CITY, address.getCity());
		values.put(AddressTable.COLUMN_COUNTRY, address.getCountry());
		values.put(AddressTable.COLUMN_STATE, address.getState());
		values.put(AddressTable.COLUMN_STREET, address.getStreet());
		values.put(AddressTable.COLUMN_ZIPCODE, address.getZipcode());
		try{
			database.update(AddressTable.TABLE_ADDRESS, values, whereClause, null);
			Log.d(TAG, "Address was updated");
		} catch (SQLException e){
			e.printStackTrace();
			Log.e(TAG, "Unable to update the address");
		}
	}

	// Update Customer
	/**
	 * Update a customer object to the database.
	 * @param customer
	 * @return 
	 */
	public void updateCustomer(Customer customer){
		// FIXME update customer to database needs to be examined
		// Open the database connection
		
		// Look up the existing customer
		ContentValues values = new ContentValues();
		String whereClause = CustomerTable.COLUMN_ID + " =  " + customer.getCustomerId();
		values.put(CustomerTable.COLUMN_NAME, customer.getCustomerName());
		try {
			database.update(CustomerTable.TABLE_CUSTOMER, values, whereClause, null);
			Log.v(TAG, "Customer was saved to database");
		} catch (Exception e){
			e.printStackTrace();
			Log.e(TAG, "Unable to save customer to database");
		}
	}
	public void updateItem(Item item) {
		// TODO Update an Item in the database
		try {
			ContentValues values = new ContentValues();
			values.put(ItemTable.COLUMN_POTENTIAL_ID, item.getPotentialId());
			values.put(ItemTable.COLUMN_ITEM_TYPE_ID, item.getItemType().getItemTypeId());
			values.put(ItemTable.COLUMN_LATITUDE, item.getLatitude());
			values.put(ItemTable.COLUMN_LONGITUDE, item.getLongitude());
			values.put(ItemTable.COLUMN_ALTITUDE, item.getAltitude());
			values.put(ItemTable.COLUMN_ELAPSED_TIME, item.getTimeElapsedMillis());
			values.put(ItemTable.COLUMN_NAME, item.getName());
			String whereClause = ItemTable.COLUMN_ID + "=" + item.getItemId();
			database.update(ItemTable.TABLE_ITEM, values, whereClause, null);
			Log.v(TAG, "Updated item id "+ item.getItemId());
		} catch (SQLException e){
			e.printStackTrace();
			Log.e(TAG, "Unable to update the item id "+item.getItemId());			
		}
	}
	public void updatePhotoAnswer(long itemId, long customerId,
			Long questionId, PhotoAnswer photoAnswer) {
		// TODO Update PhotoAnswer in the database.
		try{
			ContentValues values = new ContentValues();
			values.put(PhotoAnswerTable.COLUMN_PHOTO_ANSWER, bitmaptoByteArray(photoAnswer.getPhoto()));
			String whereClause = PhotoAnswerTable.COLUMN_ANSWER_ID + " = " + photoAnswer.getAnswerId();
			database.update(PhotoAnswerTable.TABLE_PHOTO_ANSWERS, values, whereClause, null);
			Log.v(TAG, "Updated photo id = " + photoAnswer.getAnswerId());
		} catch (SQLException e){
			e.printStackTrace();
			Log.e(TAG, "Unable to update the photo answer");
		}
		
	}
	public void updatePotential(Potential potential) {
		// TODO Auto-generated method stub
		
		ContentValues values = new ContentValues();
		String whereClause = PotentialTable.COLUMN_ID + " = " + potential.getId();
		int result = 0;
		values.put(PotentialTable.COLUMN_BUILDING_ID, potential.getBuildingId());
		values.put(PotentialTable.COLUMN_COMPLETED, potential.getComplete());
		values.put(PotentialTable.COLUMN_NAME, potential.getName());
		values.put(PotentialTable.COLUMN_PROJECTED_START, potential.getMsProjectedStartDate());
		values.put(PotentialTable.COLUMN_SCOPE_OF_WORK, potential.getScopeOfWork());
		result = database.update(PotentialTable.TABLE_POTENTIAL, values, whereClause, null);
		switch(result){
		case 0:
			Log.e(TAG, "Unable to update potential id = " + potential.getId());
			break;
		case 1:
			Log.d(TAG, "Potential ID = " + potential.getId() + " Has been updated.");
			break;
		default:
			break;
		}
		
	}

	public void updateRadioButtonAnswer(long itemId, long customerId,
			Long questionId, RadioButtonAnswer radioButtonAnswer) {
		// Update the radio button answer
		try{
			ContentValues values = new ContentValues();
			values.put(RadioButtonAnswersTable.COLUMN_ANSWER_TEXT, radioButtonAnswer.getAnswer());
			String whereClause = RadioButtonAnswersTable.COLUMN_ANSWER_ID + "=" + radioButtonAnswer.getAnswerId();
			database.update(RadioButtonAnswersTable.TABLE_RADIOBUTTON_ANSWERS, values, whereClause, null);
			Log.v(TAG, "Updated the Radio button answer id = " + radioButtonAnswer.getAnswerId());
		} catch (SQLException e){
			e.printStackTrace();
			Log.e(TAG, "Unable to update radio button answer id "+ radioButtonAnswer.getAnswerId());
		}		
	}
	public void updateSpinnerAnswer(long itemId, long customerId,
			Long questionId, SpinnerAnswer spinnerAnswer) {
		// Update the Spinner Answer
		try{
			ContentValues values = new ContentValues();
			values.put(SpinnerAnswersTable.COLUMN_OPTION_ID, spinnerAnswer.getOptionId());
			values.put(SpinnerAnswersTable.COLUMN_ANSWER_TEXT, spinnerAnswer.getAnswer());
			String whereClause = SpinnerAnswersTable.COLUMN_ANSWER_ID + "=" + spinnerAnswer.getAnswerId();
			database.update(SpinnerAnswersTable.TABLE_SPINNER_ANSWERS, values, whereClause, null);
			Log.v(TAG, "Updated the spinner answer id "+spinnerAnswer.getAnswerId());
		} catch (SQLException e) {
			e.printStackTrace();
			Log.e(TAG, "Unable to update the spinner answer id "+spinnerAnswer.getAnswerId());
		}
	}
	public void updateTextAnswer(long itemId, long customerId, Long questionId,
			TextAnswer textAnswer) {
		// Update the text answer
		try {
			ContentValues values = new ContentValues();
			values.put(TextAnswersTable.COLUMN_ANSWER_TEXT, textAnswer.getAnswerValue());
			String whereClause = TextAnswersTable.COLUMN_ANSWER_ID + "=" + textAnswer.getAnswerId();
			database.update(TextAnswersTable.TABLE_TEXT_ANSWERS, values, whereClause, null);
			Log.v(TAG, "Updated Text Answer id "+textAnswer.getAnswerId());
		} catch (SQLException e) {
			e.printStackTrace();
			Log.e(TAG, "Unable to update Text Answer id "+textAnswer.getAnswerId());
		}
		
	}
}